package com.guldan.thrall.util;


import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.guldan.core.enums.CitizenValidatingTypeEn;
import com.guldan.core.exception.BusinessException;
import com.guldan.core.utils.MathUtil;
import com.guldan.core.constants.XlsHeaderConstant;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

import com.alibaba.fastjson.JSON;
import com.guldan.jaina.dto.citizenbank.CitizenBankDTO;
import com.guldan.jaina.dto.citizeninfo.CitizenInfoDTO;


public class CitizenUploadExcel {

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
    public static final String PROCESSING = "Processing...";

    /**
     * read the Excel file
     *
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */
    public List<CitizenInfoDTO> readExcel(String path) throws IOException {
        if (path == null || EMPTY.equals(path)) {
            throw new BusinessException("没有发现上传的文件!");
        } else {
            String postfix = getPostfix(path);
            if (!EMPTY.equals(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(path);
                } else {
                    throw new BusinessException("文件格式不正确，只能为Microsoft Excel 97-2003 工作表格式，.xls后缀");
                }

            } else {
                throw new BusinessException(path + NOT_EXCEL_FILE);
            }
        }
    }


    /**
     * Read the Excel 2003-2007
     *
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    //public List<RegisterTemplateDataDTO> readXls(String path) throws IOException {
    public List<CitizenInfoDTO> readXls(String path) throws IOException {
        System.out.println(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        List<CitizenInfoDTO> list = new ArrayList<CitizenInfoDTO>();
        // Read the Sheet

        if (hssfWorkbook == null || hssfWorkbook.getNumberOfSheets() < 1 || hssfWorkbook.getSheetAt(0) == null) {
            throw new BusinessException(CitizenValidatingTypeEn.execlNoRecord.getMean(), CitizenValidatingTypeEn.execlNoRecord.getCode().toString());
        }

        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            if (numSheet == 1) {
                //只读取第一个表的信息
                break;
            }
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row


            if (hssfSheet.getPhysicalNumberOfRows() < 3) {
                throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldName.getMean(), "人员记录")
                        , CitizenValidatingTypeEn.emptyFieldName.getCode().toString());
            }

            //读取headers，
            HSSFRow hssfRow = hssfSheet.getRow(1);
            int total = hssfRow.getPhysicalNumberOfCells();
            //校验header title
            List<String> xlsTitleList = new ArrayList<>();
            for (int i = 0; i < total; i++) {
                String temp = getStringCellValue(hssfRow.getCell(i));
                xlsTitleList.add(temp);
            }
            //校验固定列是否都存在
            for (String h : XlsHeaderConstant.CITIZEN_XLS_HEADERS) {
                if (!xlsTitleList.contains(h)) {
                    throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldName.getMean(), h)
                            , CitizenValidatingTypeEn.emptyFieldName.getCode().toString());
                }
            }
            //校验header title end


            for (int rowNum = 2; rowNum <= hssfSheet.getPhysicalNumberOfRows(); rowNum++) {

                try {

                    hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow != null) {
                        CitizenInfoDTO dataDto = new CitizenInfoDTO();

                        if (isEndRow(hssfRow)) {
                            break;
                        }

                        int index = 0;
                        //获取 姓名
                        String name = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(name)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        /*if (name.contains(" ")) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.notAllowBlankError.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.notAllowBlankError.getCode().toString());
                        }*/

                        //获取 姓名（藏）
                        index = 1;
                        String nameZw = getStringCellValue(hssfRow.getCell(index));

                        //获取 民族*
                        index = 2;
                        String nationName = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(nationName)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取 学历*
                        index = 3;
                        String degreeName = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(degreeName)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取 证件类型*
                        index = 4;
                        String identTypeName = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(identTypeName)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取 证件号码*
                        index = 5;
                        String identNumber = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(identNumber)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取 开户银行名称*
                        index = 6;
                        String bankName = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(bankName)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取 开户银行卡号*
                        index = 7;
                        String bankNumber = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(bankNumber)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        } else {
                            //校验是否为数字
                            if (!MathUtil.isInteger(bankNumber)) {
                                throw new BusinessException(String.format(CitizenValidatingTypeEn.formatErrorByRow.getMean()
                                        , rowNum - 1
                                        , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                        , CitizenValidatingTypeEn.formatErrorByRow.getCode().toString());
                            }
                        }*/


                        //获取 用户归属行政区划*
                        index = 8;
                        String areaName = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(areaName)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取 户口簿编号*
                        index = 9;
                        String householdRegisterNumber = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(householdRegisterNumber)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        } else {
                            //校验是否为数字+字母
                            if (!MathUtil.isNumberAndChar(householdRegisterNumber)) {
                                throw new BusinessException(String.format(CitizenValidatingTypeEn.formatErrorByRow.getMean()
                                        , rowNum - 1
                                        , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                        , CitizenValidatingTypeEn.formatErrorByRow.getCode().toString());
                            }
                        }*/

                        //获取 是否户主*
                        index = 10;
                        String ism = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(ism)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        Boolean isMaster = null;
                        if (ism.equals("1")) {
                            isMaster = true;
                        } else if (ism.equals("0")) {
                            isMaster = false;
                        } else {
//                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
//                                    , rowNum - 1
//                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
//                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }


                        //获取 与户主关系*
                        index = 11;
                        String relationName = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(relationName)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取 户主证件号码*
                        index = 12;
                        String householdNumber = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(householdNumber)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        } else if (isMaster) {
                            //是户主，则户主关系只能为“户主”
                            if (!relationName.equals("户主") || !householdNumber.equals(identNumber)) {
                                throw new BusinessException(String.format(CitizenValidatingTypeEn.isOnlyMaster.getMean(), rowNum - 1));
                            }

                        } else {
                            //则户主关系只能为户主之外的，且户主证件号码与本人证件号码不能相同；
                            if (relationName.equals("户主") || householdNumber.equals(identNumber)) {
                                throw new BusinessException(String.format(CitizenValidatingTypeEn.isNotMaster.getMean(), rowNum - 1));
                            }
                        }*/


                        //获取 人员分类*
                        index = 13;
                        String citizenInfoTypeName = getStringCellValue(hssfRow.getCell(index));
                        /*if (StringUtils.isBlank(citizenInfoTypeName)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.emptyFieldByName.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.emptyFieldByName.getCode().toString());
                        }*/

                        //获取 户口类别
                        index = 14;
                        String houseHoldTypeName = getStringCellValue(hssfRow.getCell(index));


                        //获取 家庭地址
                        index = 15;
                        String address = getStringCellValue(hssfRow.getCell(index));

                        //获取 家庭住址（藏）
                        index = 16;
                        String addressZw = getStringCellValue(hssfRow.getCell(index));

                        //获取 电话号码
                        index = 17;
                        String telephone = getStringCellValue(hssfRow.getCell(index));
                        /*//校验是否为数字
                        if (!MathUtil.isInteger(telephone)) {
                            throw new BusinessException(String.format(CitizenValidatingTypeEn.formatErrorByRow.getMean()
                                    , rowNum - 1
                                    , XlsHeaderConstant.CITIZEN_XLS_HEADERS[index])
                                    , CitizenValidatingTypeEn.formatErrorByRow.getCode().toString());
                        }*/

                        //获取 是否校验身份证格式
                        index = 18;
                        String checkIdCarkStr = getStringCellValue(hssfRow.getCell(index));
                        Boolean checkIdCark = null;
                        if (checkIdCarkStr.equals("1")) {
                            checkIdCark = false;
                        } else {
                            checkIdCark = true;
                        }


                        dataDto.setName(name);
                        dataDto.setNameZw(nameZw);
                        dataDto.setNationName(nationName);
                        dataDto.setDegreeName(degreeName);
                        dataDto.setIdentTypeName(identTypeName);
                        dataDto.setIdentNumber(identNumber);

                        List<CitizenBankDTO> bankList = new ArrayList<CitizenBankDTO>();
                        CitizenBankDTO citizenBankDTO = new CitizenBankDTO();
                        citizenBankDTO.setBankName(bankName);
                        citizenBankDTO.setBankNumber(bankNumber);
                        bankList.add(citizenBankDTO);
                        dataDto.setCitizenBankList(bankList);

                        dataDto.setAreaName(areaName);
                        dataDto.setHouseholdRegisterNumber(householdRegisterNumber);
                        dataDto.setMaster(isMaster);
                        dataDto.setRelationName(relationName);
                        dataDto.setHouseholdNumber(householdNumber);
                        dataDto.setCitizenInfoTypeName(citizenInfoTypeName);
                        dataDto.setHouseHoldTypeName(houseHoldTypeName);
                        dataDto.setAddress(address);
                        dataDto.setAddressZw(addressZw);
                        dataDto.setTelephone(telephone);

                        dataDto.setNeedCheckIdCard(checkIdCark);

                        list.add(dataDto);
                    }
                } catch (BusinessException ex) {
                    ex.printStackTrace();
                    throw ex;
                }
            }
        }

        if (list == null || list.size() == 0) {
            throw new BusinessException("execl中未找到到人员记录");
        }

        return list;
    }

    private String getStringCellValue(HSSFCell cell) {
        if (cell != null) {
            cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
            return cell.getStringCellValue() != null ? cell.getStringCellValue() : "";
        }
        return "";
    }

    private double getNumericCellValue(HSSFCell cell) {
        if (cell != null) {
            cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC);
            try {
                return cell.getNumericCellValue();
            } catch (Exception ex) {
                return 0;
            }
        }
        return 0;
    }

    private Boolean getBooleanCellValue(HSSFCell cell) {
        if (cell != null) {
            cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN);

            try {
                return cell.getBooleanCellValue();
            } catch (Exception ex) {
                return null;
            }
        }
        return null;
    }

    @SuppressWarnings("static-access")
    private String getValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    private Boolean isEndRow(HSSFRow hssfRow) {
        boolean ret = true;
        int total = hssfRow.getPhysicalNumberOfCells();
        for (int i = 0; i < total; i++) {
            if (StringUtils.isNotBlank(getStringCellValue(hssfRow.getCell(i)))) {
                ret = false;
                break;
            }
        }
        return ret;
    }

    public static void main(String[] args) {
        CitizenUploadExcel excel = new CitizenUploadExcel();
        String filePath = "G:\\citizen_template.xls";
        try {
            List<CitizenInfoDTO> list = excel.readExcel(filePath);

            if (list != null)
                System.out.println(JSON.toJSONString(list));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public String getPostfix(String path) {
        if (path == null || EMPTY.equals(path.trim())) {
            return EMPTY;
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return EMPTY;
    }
}